﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using Entities;

namespace DataAccess
{
	public class TagDAL
	{
		private string connectionString = "Data Source=" + new System.IO.DirectoryInfo(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + "\\DoubleFeature").FullName + "\\Resources\\movies.sqlite";

		public bool Save(Tag entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("INSERT INTO tags (tag_name) VALUES (?)", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Name;

				cmd.Parameters.Add(param1);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

        public bool Update(Tag entity)
        {
            try
            {
                SQLiteConnection conn = new SQLiteConnection(connectionString);

                SQLiteCommand cmd = new SQLiteCommand("UPDATE tags SET tag_name=? WHERE tag_id=?", conn);

                SQLiteParameter param1 = new SQLiteParameter();
                param1.Value = entity.Name;
                SQLiteParameter param2 = new SQLiteParameter();
                param2.Value = entity.Id;

                cmd.Parameters.Add(param1);
                cmd.Parameters.Add(param2);

                conn.Open();

                cmd.ExecuteNonQuery();

                conn.Close();

                return true;
            }
            catch (Exception e)
            {
                //log
                return false;
            }
        }

        public bool Delete(Tag entity)
        {
            try
            {
                SQLiteConnection conn = new SQLiteConnection(connectionString);

                SQLiteCommand cmd = new SQLiteCommand("DELETE FROM tags WHERE tag_id=?", conn);

                SQLiteParameter param1 = new SQLiteParameter();
                param1.Value = entity.Id;

                cmd.Parameters.Add(param1);

                conn.Open();

                cmd.ExecuteNonQuery();

                cmd.CommandText = "DELETE FROM relationships WHERE relationship_element=" + entity.Id + " AND relationship_type='tag'";

                cmd.ExecuteNonQuery();

                conn.Close();

                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }

		public bool AddMovie(Tag entity, Movie movie)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("INSERT INTO relationships (movie_id, relationship_element, relationship_type) VALUES (?, ?, 'tag')", conn);

				SQLiteParameter paramMovieIndex = new SQLiteParameter();
				paramMovieIndex.Value = movie.Id;
				SQLiteParameter paramTagIndex = new SQLiteParameter();
				paramTagIndex.Value = entity.Id;

				cmd.Parameters.Add(paramMovieIndex);
				cmd.Parameters.Add(paramTagIndex);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool DeleteMovie(Tag entity, Movie movie)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("DELETE FROM relationships WHERE movie_id=? AND relationship_element=? AND relationship_type='tag'", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = movie.Id;
				SQLiteParameter param2 = new SQLiteParameter();
				param2.Value = entity.Id;

				cmd.Parameters.Add(param1);
				cmd.Parameters.Add(param2);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public Tag GetByName(string name)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM tags WHERE tag_name=?", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = name;

				cmd.Parameters.Add(param1);

				conn.Open();

				SQLiteDataReader dr = cmd.ExecuteReader();

				Tag tag = new Tag();

				if (dr.HasRows)
				{
					dr.Read();
					tag.Id = Convert.ToInt32(dr["tag_id"]);
					tag.Name = dr["tag_name"].ToString();
				}
				else
				{
					tag = null;
				}

				conn.Close();

				return tag;
			}
			catch (Exception e)
			{
				//log
				return null;
			}
		}

		public Tag GetLastAdded()
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM tags ORDER BY tag_id DESC LIMIT 1", conn);

				conn.Open();

				SQLiteDataReader dr = cmd.ExecuteReader();

				Tag tag = new Tag();

				if (dr.HasRows)
				{
					dr.Read();
					tag.Id = Convert.ToInt32(dr["tag_id"]);
					tag.Name = dr["tag_name"].ToString();
				}
				else
				{
					tag = null;
				}

				conn.Close();

				return tag;
			}
			catch (Exception e)
			{
				//log
				return null;
			}
		}

		public List<Tag> List()
		{
			SQLiteConnection conn = new SQLiteConnection(connectionString);
			SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM tags ORDER BY tag_name ASC", conn);

			conn.Open();

			List<Tag> collection = new List<Tag>();

			SQLiteDataReader dr = cmd.ExecuteReader();

			while (dr.Read())
			{
				Tag obj = new Tag();

				obj.Id = Convert.ToInt32(dr["tag_id"]);
				obj.Name = dr["tag_name"].ToString();

				collection.Add(obj);
			}

			conn.Close();

			return collection;
		}
	}
}
